Customer Case Study · HR · Future of Work
As the power transmission company planned its workplace after the pandemic, HMC ran a remote workplace survey end to end. Managers gave their views and assessed each team member's role in a validated Excel form. A macro consolidated every response file, Power Query shaped the data, and Power BI turned it into a remote-readiness index for every role.
Figures from the Power BI dashboard shown below.
Leadership needed to decide who works from home, from a company office or from a satellite office near home. Preferences alone would not do; the answer depended on what each role actually needs.
Some roles need office infrastructure or in-person meetings; others can run fully remote.
Every manager had to answer the same questions, rank options properly and assess each team member.
Each manager returned their own protected workbook, saved under their own name.
Answers had to become one score per role that could be sliced by BU, function, grade and life stage.
One pipeline from the manager's inbox to the leadership dashboard.
Select a step to see what happened.
Worked with HR leadership on the decision to be made.
Deliverable: Survey blueprint and index factors.
Manager views plus a role-by-role assessment.
Deliverable: Questionnaire and scoring rules.
Every step automated.
Deliverable: Survey workbook, macro, Power BI report.
Managers filled the form and mailed it back.
Deliverable: Consolidated responses.
The dashboard drove the workplace plan.
Deliverable: Workplace recommendation pack.
The original dashboard and process map first, then five working modules: fill the survey, run the consolidation, design the index, explore the results, and see how Power Query shaped it all.
Shown as delivered. Names and email addresses in the process map are pixelated. Click to enlarge.
A working replica of the Excel VBA survey. Log in, answer Section 01 (try breaking the ranking rules), assess your team in Section 02 and watch each role's index update, then save it the way managers did.
Login, names and email are synthetic. Questions follow the original form.
Run the consolidation macro over the returned files, then step through the Power Query logic that separates rankings and scores Section 02.
Step names from the Power Query notes in the process map; sample rows are synthetic.
The index blends five factors with custom weights. Change the weights and the cut-offs to see how roles move between bands, and how often a simple rule agrees with what managers chose.
Index logic follows the "5 conditional columns" and "custom column weights" steps; the weights shown are illustrative and calibrated to the reported average, minimum and maximum. Synthetic roles.
A working replica of the report on 650 synthetic roles and 130 managers, calibrated to the original: work from home 52.62%, company office 26.00%, satellite 21.38%. Click bars, tiles or bubbles to cross-filter.
Manager views respond to the BU and Function filters.
Synthetic roles and managers; best-option shares and index average, minimum and maximum match the report at the default weights. Functions, sub-functions, locations, life stages and manager answers are illustrative.
Validation at entry, one-click consolidation and a ready model meant HR spent its time on the decision, not on the data.
Hours of HR effort per survey round
Cumulative files processed over working hours
Figures indicative; validated metrics available on request.
Asking managers what each role needs gave a defensible basis for who works where.
Unique ranks and complete answers at submit time removed most of the cleaning later.
Five named factors and visible weights let leadership see, and challenge, how the index works.